package sfy.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

//堵塞类
public class Block implements Runnable {
	private Connection conn;

	public void connect() {

	}

	// 检查是否有死锁
	public List<BlockData> inspectionLock() {
		List<BlockData> blockDatas = new ArrayList<>();
		UrlEntity ue = new UrlEntity();
		ue.setUrl("jdbc:oracle:thin:@10.10.12.41:1521:sfytest");
		conn = DataBase.getConn();
		System.out.println("数据库连接成功");
		StringBuffer buffer = new StringBuffer("Select b_s.SQL_ID blocked_sql_id,");
		buffer.append(
				"'节点 ' || a_s.INST_ID || ' session ' || a_s.sid || ',' || a_s.SERIAL# ||' 阻塞了 节点 ' || b_s.INST_ID || ' session ' || b_s.SID || ',' ||");
		buffer.append(" b_s.SERIAL# blockinfo,");
		buffer.append("a_s.INST_ID,");
		buffer.append("a_s.SID,");
		buffer.append("a_s.SCHEMANAME,");
		buffer.append("a_s.MODULE,");
		buffer.append("a_s.STATUS,");
		buffer.append("'后为被阻塞信息',");
		buffer.append("b_s.INST_ID blocked_inst_id,");
		buffer.append("b_s.SID blocked_sid,");
		buffer.append("b_s.SCHEMANAME blocked_SCHEMANAME,");
		buffer.append("b_s.EVENT blocked_event,");
		buffer.append("b_s.MODULE blocked_module,");
		buffer.append("b_s.STATUS blocked_status,");
		buffer.append("b_s.SQL_ID blocked_sql_id,");
		buffer.append("obj.owner blocked_owner,");
		buffer.append("obj.object_name blocked_object_name,");
		buffer.append("obj.OBJECT_TYPE blocked_OBJECT_TYPE, ");
		buffer.append("case ");
		buffer.append("when b_s.ROW_WAIT_OBJ# <> -1 then ");
		buffer.append("dbms_rowid.rowid_create(1,");
		buffer.append("obj.DATA_OBJECT_ID,");
		buffer.append("b_s.ROW_WAIT_FILE#,");
		buffer.append("b_s.ROW_WAIT_BLOCK#,");
		buffer.append("b_s.ROW_WAIT_ROW#)");
		buffer.append(" else ");
		buffer.append("'-1'");
		buffer.append(" end blocked_rowid,");
		buffer.append(" decode(obj.object_type,");
		buffer.append("'TABLE',");
		buffer.append("'select * from ' || obj.owner || '.' || obj.object_name ||");
		buffer.append("' where rowid=''' ||");
		buffer.append("dbms_rowid.rowid_create(1,");
		buffer.append("obj.DATA_OBJECT_ID,");
		buffer.append("b_s.ROW_WAIT_FILE#,");
		buffer.append("b_s.ROW_WAIT_BLOCK#,");
		buffer.append("b_s.ROW_WAIT_ROW#) || '''',");
		buffer.append("NULL) blocked_data_querysql");
		buffer.append(" from gv$session a_s,");
		buffer.append("gv$session b_s,");
		buffer.append("dba_objects obj");
		buffer.append(" where b_s.BLOCKING_INSTANCE is not null");
		buffer.append(" and b_s.BLOCKING_SESSION is not null");
		buffer.append(" and a_s.INST_ID = b_s.BLOCKING_INSTANCE");
		buffer.append(" and a_s.SID = b_s.BLOCKING_SESSION");
		buffer.append(" and b_s.ROW_WAIT_OBJ# = obj.object_id(+)");
		buffer.append(" order by a_s.inst_id, a_s.sid");
		System.out.println(buffer.toString());
		Statement stm;
		try {
			stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(buffer.toString());
			while (rs.next()) {
				BlockData blockData = new BlockData();
				blockData.setBlockInfo(rs.getString("BLOCKINFO"));
				blockData.setServerNum(rs.getString("INST_ID"));
				blockDatas.add(blockData);
			}
			stm.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return blockDatas;
	}

	public void Unlock(){
		List<BlockData> blockDatas=inspectionLock();
		UrlEntity ue=new UrlEntity();
		for (BlockData blockData : blockDatas) {
			if(blockData.getServerNum().trim().equals("1"))
			{
				ue.setUrl("jdbc:oracle:thin:@172.16.1.9:1521:sfytest");
				System.out.println(DataBase.url);
				conn=DataBase.getConn();
				System.out.println("数据库连接成功");
			String str=blockData.getBlockInfo();
			String session=getSession(str);
			String sql="alter system kill session '"+session+"'";
			try {
				Statement stm=conn.createStatement();
				stm.executeQuery(sql);
				stm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
			else{
				ue.setUrl("jdbc:oracle:thin:@172.16.1.10:1521:sfytest");
				System.out.println(DataBase.url);
				conn=DataBase.getConn();
				System.out.println("数据库连接成功");
			String str=blockData.getBlockInfo();
			String session=getSession(str);
			String sql="alter system kill session '"+session+"'";
			try {
				Statement stm=conn.createStatement();
				stm.executeQuery(sql);
				stm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
			
//			String nextStr=str.split("");
		}
	}

	public String getSession(String str) {
		String[] strs = str.split("n");
		String s = strs[1] + strs[2];
		System.out.println(s);
		String[] ss = s.split("阻");
		for (int i = 0; i < ss.length; i++) {
			System.out.println(ss[i]);
		}
		System.out.println(ss[0] + "*********************");
		String finals = ss[0].trim();
		System.out.println(finals);
		return finals;
	}

	@Override
	public void run() {
		while (true) {
			Unlock();
		}
	}

}
